-- EXEC sp_sort_bin_history_report 'sort','0','0','0','0','0','XLREPORT', '0'
ALTER  PROC SP_SORT_BIN_HISTORY_REPORT (
 
@PROCESS_TYPE		VARCHAR(50),
@PROCESSSTEP_IDS	VARCHAR(100),
@BASEPRODUCT_ID		VARCHAR(50),
@VENDOR			    VARCHAR(100),
@FROMDATE		    VARCHAR(50),
@TODATE 		    VARCHAR(50),
@REPORT_TYPE		VARCHAR(50),
@DEFAULT_PROMPT		VARCHAR(50) 
)
 
 AS
 	DECLARE @QTY_IN INT, @QTY_OUT INT, @YIELD FLOAT,@DIE_WAFER INT
 	DECLARE @DATE_IN DATETIME, @DATE_OUT DATETIME
 	DECLARE @TEMP_LOT_ID INT, @WAFER_QTY INT
 	DECLARE @LOT_NUMBER VARCHAR(50), @VENDOR_NAME VARCHAR(50)
 	DECLARE @TEMP_PROCESS_TYPE VARCHAR(15)	
 	DECLARE @BIN1 INT, @BIN2 INT, @BIN3 INT, @BIN4 INT, @BIN5 INT, @BIN6 INT, @BIN7 INT, @BIN8 INT, @BIN9 INT, @BIN10 INT
 	DECLARE @BIN11 INT,@BIN12 INT, @BIN13 INT,@BIN14 INT,@BIN15 INT, @SORT_PROCESS_STEP_ID INT
 	DECLARE @WAFER_ID INT, @MARKETING_PART_NUMBER VARCHAR(50)
 	DECLARE @GOODBINS INT,@BADBINS INT 
 	DECLARE @GOODDICE INT
 	DECLARE @PROSTEPID INT
 	DECLARE @ALL_PROCESS_STEPS INT
        DECLARE @QTYOUT INT
	DECLARE @BIN_REJECTED INT

	DECLARE @VENDOR_LOT VARCHAR(1000)
  	
 	-- TEMPORARTY TABLE TO DISPLAY THE RESULT OF BINS INFORMATION WITH LOTNUMBER AND PARTNUMBER FOR THE WAFERS
 	DECLARE @RESULTS TABLE(MARKETINGPARTNUMBER VARCHAR(25), LOT_NUMBER VARCHAR(50),WAFER_ID INT,TOTAL INT,
 				GOODBINS INT,BADBINS INT,BIN_1 INT,BIN_2 INT,BIN_3 INT,BIN_4 INT,BIN_5 INT,BIN_6 INT,BIN_7 INT,BIN_8 INT,
 					BIN_9 INT,BIN_10 INT,BIN_11 INT,BIN_12 INT,BIN_13 INT,BIN_14 INT,BIN_15 INT,DATE_IN 
 						DATETIME, DATE_OUT DATETIME,YIELD FLOAT,GROSSDIE INT , VENDOR_LOT VARCHAR(1000), BIN_REJECTED INT, PS_TYPE VARCHAR(50), VENDOR_NAME VARCHAR(50) )
 	
 	DECLARE @SQLSTRING NVARCHAR(2000)
 	DECLARE @PARAMDEFINITION NVARCHAR(500)
 
 	SET @SQLSTRING = N' DECLARE LOTCURSOR CURSOR FOR SELECT P.PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, L_VENDOR_NAME, LT.LOT_ID,LOT_NUMBER,WAFER_QTY_IN,DATE_IN,DATE_OUT,GOOD_DIE_QTY,SL_YIELD 
						FROM PRODUCT P, PROCESS_STEP PS, LOT_TABLE LT, LOCATION L, SORT_LOT SL 
					WHERE PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID AND LT.LOT_ID = SL.LOT_ID
						 AND LOT_CURRENT_STATUS <>''Running''AND ( P_TYPE IS NULL OR P_TYPE = ''BOARD'' OR P_TYPE = ''KIT'' )'
 	
 	--SET @PARAMDEFINITION = N'@PRM_PROCESSSTEP_ID INT, @PRM_PROCESS_TYPE VARCHAR(15), @PRM_LOT_NO VARCHAR(50)'
 	
 	IF ( @PROCESS_TYPE IS NOT NULL  AND @PROCESS_TYPE <> '0' )
	BEGIN
			SET @SQLString = @SQLSTRING + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 
	END

	IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.PROCESSSTEP_ID = ''' +  @PROCESSSTEP_IDS + '''' 
	END 

	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P_PARENT_PRODUCT_ID =  ''' +  @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' and L.VENDOR_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING (  ''' + @VENDOR + '''' +') )'
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.DATE_OUT >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END

	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.DATE_OUT <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
 
 	SET @SQLSTRING = @SQLSTRING + ' ORDER BY PART_NUMBER,DATE_IN,DATE_OUT '
 
 	PRINT @SQLSTRING
 	EXECUTE SP_EXECUTESQL @SQLSTRING --, @PARAMDEFINITION, @PRM_PROCESSSTEP_ID = @PROCESS_STEP_ID, @PRM_PROCESS_TYPE = @PROCESS_STEP_TYPE, @PRM_LOT_NO = @LOT_NO
 
 	OPEN LOTCURSOR 
 	FETCH NEXT FROM LOTCURSOR INTO @SORT_PROCESS_STEP_ID, @MARKETING_PART_NUMBER, @TEMP_PROCESS_TYPE,@VENDOR_NAME,
 			@TEMP_LOT_ID, @LOT_NUMBER, @WAFER_QTY, @DATE_IN, @DATE_OUT, @QTYOUT, @YIELD
 
 	WHILE @@FETCH_STATUS = 0
 	BEGIN
		
 		DECLARE @VALIDBINSCOUNT INT
		SET @VALIDBINSCOUNT = 0;
 		
 		DECLARE WAFERCURSOR CURSOR FOR
 
 		SELECT WAFER_ID,YIELD,BIN_1,BIN_2,BIN_3,BIN_4,BIN_5,BIN_6,BIN_7,BIN_8,BIN_9,BIN_10,BIN_11,BIN_12,BIN_13,BIN_14,
 						         BIN_15, BIN_REJECTED FROM INFORMATIVE_BINNING WHERE LOT_ID = @TEMP_LOT_ID 
  
 		OPEN WAFERCURSOR  
 
 		FETCH NEXT FROM WAFERCURSOR INTO @WAFER_ID,@YIELD,@BIN1,@BIN2,@BIN3,@BIN4,@BIN5,@BIN6,@BIN7,@BIN8,@BIN9,
 								      @BIN10,@BIN11,@BIN12,@BIN13,@BIN14,@BIN15,@BIN_REJECTED
 
 		WHILE @@FETCH_STATUS = 0
 		BEGIN
 
 			IF( @BIN1 IS NOT NULL )				 
 			BEGIN
 				SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END 
 			
 			IF( @BIN2 IS NOT NULL )				 
 			BEGIN
 			   	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END 	
 			IF( @BIN3 IS NOT NULL )				 
 			BEGIN
 			   	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END 	
 			IF( @BIN4 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END 	
 			IF( @BIN5 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END 	
 			IF( @BIN6 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END
 			IF( @BIN7 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END 	
 			IF( @BIN8 IS NOT NULL )				 
 			BEGIN
 			   	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END 	
 			IF( @BIN9 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END
 			IF( @BIN10 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END
 			IF( @BIN11 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END 
 			IF( @BIN12 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END
 			IF( @BIN13 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END 
 			IF( @BIN14 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END	
 			IF( @BIN15 IS NOT NULL )				 
 			BEGIN
 			    	SET @VALIDBINSCOUNT = @VALIDBINSCOUNT + 1
 			END
 			
			IF @VALIDBINSCOUNT = 0
			    SET @GOODBINS = 1
			SET @GOODBINS = @VALIDBINSCOUNT
 
 
 			INSERT INTO @RESULTS (MARKETINGPARTNUMBER,LOT_NUMBER,WAFER_ID,TOTAL,GOODBINS,BADBINS,BIN_1,BIN_2,BIN_3,BIN_4,BIN_5,
 						       BIN_6,BIN_7,BIN_8,BIN_9,BIN_10, BIN_11,BIN_12,BIN_13,BIN_14,BIN_15,
 						       DATE_IN,DATE_OUT,YIELD,GROSSDIE, VENDOR_LOT, BIN_REJECTED, PS_TYPE, VENDOR_NAME) 
 			VALUES(@MARKETING_PART_NUMBER,@LOT_NUMBER,@WAFER_ID,@QTYOUT,@GOODBINS,@BADBINS,@BIN1,@BIN2,@BIN3,@BIN4,@BIN5,@BIN6,
 				            @BIN7,@BIN8,@BIN9,@BIN10,@BIN11,@BIN12,@BIN13,@BIN14,@BIN15,@DATE_IN,
 					@DATE_OUT,@YIELD,@DIE_WAFER, @VENDOR_LOT, @BIN_REJECTED, @TEMP_PROCESS_TYPE ,@VENDOR_NAME)	
 				
 
 			FETCH NEXT FROM WAFERCURSOR INTO @WAFER_ID,@YIELD,@BIN1,@BIN2,@BIN3,@BIN4,@BIN5,@BIN6,@BIN7,@BIN8,
 								     @BIN9,@BIN10,@BIN11,@BIN12,@BIN13,@BIN14,@BIN15,@BIN_REJECTED	
 		 
 		END
 		CLOSE WAFERCURSOR
 		DEALLOCATE WAFERCURSOR
 
 	FETCH NEXT FROM LOTCURSOR INTO @SORT_PROCESS_STEP_ID, @MARKETING_PART_NUMBER, @TEMP_PROCESS_TYPE,@VENDOR_NAME,
 			@TEMP_LOT_ID, @LOT_NUMBER, @WAFER_QTY, @DATE_IN, @DATE_OUT, @QTYOUT, @YIELD
 	END
 	CLOSE LOTCURSOR
 	DEALLOCATE LOTCURSOR

 	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
		SELECT
		MARKETINGPARTNUMBER AS [MARKETING PARTNUMBER] , 
		LOT_NUMBER AS [LOT NUMBER],
		VENDOR_NAME AS [VENDOR],
		PS_TYPE AS [ PROCESS],
		WAFER_ID  AS [WAFER ID],
		YIELD  ,
		BIN_1 AS[BIN 1], 
		BIN_2 AS[BIN 2],
		BIN_3 AS[BIN 3],
		BIN_4 AS[BIN 4],
		BIN_5 AS[BIN 5 ],
		BIN_REJECTED AS [BIN REJECTED] 
		FROM @RESULTS ORDER BY MARKETINGPARTNUMBER,LOT_NUMBER,WAFER_ID
	END
	ELSE
	BEGIN
		SELECT * FROM @RESULTS ORDER BY MARKETINGPARTNUMBER,LOT_NUMBER,WAFER_ID
	END